namespace SubhadraSolutions.Sharp.Logging.SQLite.LogSubscribers
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SQLite;
    using System.Text;

    using SubhadraSolutions.Sharp.Logging.Common;
    using SubhadraSolutions.Sharp.Logging.Contracts;
    using SubhadraSolutions.Sharp.Logging.LogSubscribers.FileSystem;

    public class SQLiteLogSubscriber : AbstractFileLogSubscriberCore
    {
        #region Fields

        private SQLiteCommand _command;
        private string _connectionString;
        private string _createTableCommandText;

        #endregion Fields

        #region Constructors

        public SQLiteLogSubscriber()
        {
            DoRollingAtApplicationStart = false;
            TableName = "LOGS";
            Reader = new LogStoreItemDataReader();
        }

        #endregion Constructors

        #region Properties

        public string ConnectionStringFormat
        {
            get; set;
        }

        public ILoggerDataReader Reader
        {
            get; set;
        }

        public string TableName
        {
            get; set;
        }

        #endregion Properties

        #region Methods

        protected override void createNewFile()
        {
            SQLiteConnection.CreateFile(_writingFileInfo.FullName);
            using (var conn = new SQLiteConnection(_connectionString))
            {
                var command = new SQLiteCommand(_createTableCommandText, conn);
                conn.Open();
                command.ExecuteNonQuery();
            }
        }

        protected override void initializeProtected()
        {
            if (Reader != null)
            {
                Reader.SetProcessInstanceName(ProcessInstanceName);
                Reader.Initialize();
            }
            base.initializeProtected();
            _connectionString = string.Format(ConnectionStringFormat, _writingFileInfo.FullName);
            _command = createCommands();
            _command.Prepare();
        }

        protected override void writeInfoFile(IEnumerable<LogStoreItem> e)
        {
            _writingFileInfo.Refresh();
            if (!_writingFileInfo.Exists)
            {
                createNewFile();
            }
            var connection = new SQLiteConnection(_connectionString);
            using (connection)
            {
                Reader.SetEnumerator(e.GetEnumerator(), false);
                connection.Open();
                SQLiteTransaction transaction =
                    connection.BeginTransaction(IsolationLevel.Serializable);
                try
                {
                    _command.Connection = connection;
                    _command.Transaction = transaction;
                    while (Reader.Read())
                    {
                        for (int i = 0; i < _command.Parameters.Count; i++)
                        {
                            _command.Parameters[i].Value = Reader[i];
                        }
                        _command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                }
                finally
                {
                    _command.Connection = null;
                    _command.Transaction = null;
                }
            }
        }

        private static SQLiteParameter addParameter(SQLiteCommand command, string parameterName,
            DbType dbType)
        {
            SQLiteParameter parameter = command.CreateParameter();
            parameter.ParameterName = '@' + parameterName;
            parameter.DbType = dbType;
            command.Parameters.Add(parameter);
            return parameter;
        }

        private static string mapColumnType(Type columnType)
        {
            if (columnType == typeof (int) || columnType == typeof (int?))
            {
                return "INTEGER";
            }
            if (columnType == typeof (short) || columnType == typeof (short?))
            {
                return "SMALLINT";
            }
            if (columnType == typeof (long) || columnType == typeof (long?))
            {
                return "BIGINT";
            }
            if (columnType == typeof (float) || columnType == typeof (float?))
            {
                return "FLOAT";
            }
            if (columnType == typeof (double) || columnType == typeof (double?))
            {
                return "DOUBLE";
            }
            if (columnType == typeof (DateTime) || columnType == typeof (DateTime?))
            {
                return "DATETIME";
            }
            return "TEXT";
        }

        private static DbType mapDbType(Type columnType)
        {
            if (columnType == typeof (int) || columnType == typeof (int?))
            {
                return DbType.Int32;
            }
            if (columnType == typeof (short) || columnType == typeof (short?))
            {
                return DbType.Int16;
            }
            if (columnType == typeof (long) || columnType == typeof (long?))
            {
                return DbType.Int64;
            }
            if (columnType == typeof (float) || columnType == typeof (float?) ||
                columnType == typeof (double) ||
                columnType == typeof (double?))
            {
                return DbType.Double;
            }
            if (columnType == typeof (DateTime) || columnType == typeof (DateTime?))
            {
                return DbType.DateTime;
            }
            return DbType.String;
        }

        private SQLiteCommand createCommands()
        {
            var command = new SQLiteCommand();
            var sbCreate = new StringBuilder();
            var sbColumns = new StringBuilder();
            var sbValues = new StringBuilder();
            for (int i = 0; i < Reader.FieldCount; i++)
            {
                string columnName = Reader.GetName(i);
                columnName = columnName.Replace(' ', '_');
                Type columnType = Reader.GetFieldType(i);
                DbType dbType = mapDbType(columnType);
                string columnDataType = mapColumnType(columnType);
                sbCreate.Append(columnName);
                sbCreate.Append(' ');
                sbCreate.Append(columnDataType);
                sbCreate.Append(',');
                sbColumns.Append(columnName);
                sbColumns.Append(',');
                sbValues.Append('@');
                sbValues.Append(columnName);
                sbValues.Append(',');
                addParameter(command, columnName, dbType);
            }
            sbCreate.Length--;
            sbColumns.Length--;
            sbValues.Length--;
            _createTableCommandText = string.Format("CREATE TABLE {0} ({1})", TableName, sbCreate);
            command.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})", TableName,
                sbColumns, sbValues);
            return command;
        }

        #endregion Methods
    }
}